package br.com.linkcom.sined.geral.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PedagioDAO {
	
	private static final String DRIVER_POSTGRE = "org.postgresql.Driver";	
	
	public static Connection getConexaoPostgre() throws Exception {
		
		try {
			String url = "jdbc:postgresql://localhost/pedagio";
			String login = "postgres";
			String senha = "123456";
			Class.forName(DRIVER_POSTGRE);
			return DriverManager.getConnection(url, login, senha);
		}catch (Exception e) {
			throw e;
		}
	}
	
	public static void insertPasseLivre(String data, String placa) throws Exception {
		
		String sql = "insert into passelivreveiculo (cdpasselivreveiculo, data, placa) values " +
					 "(nextval('sq_passelivreveiculo'), '" + data + "','" + placa + "')";
		
		Connection conexao = getConexaoPostgre();
		Statement statement = conexao.createStatement();
		statement.execute(sql);
		conexao.close();
	}
	
	public static boolean isPossuiPasseLivre(String placa) throws Exception {
		
		String sql = "select count(*) from passelivreveiculo plv where upper(plv.placa)='" + placa.toUpperCase() + "' and plv.data+30>='now'::date";

		Connection conexao = getConexaoPostgre();
		Statement statement = conexao.createStatement();
		statement.execute(sql);
		ResultSet resultSet = statement.executeQuery(sql);
		Integer total = null;
		
		while (resultSet.next()){
			total = resultSet.getInt(1);
			break;
		}
		
		conexao.close();
		
		return total>0;
	}
}
